package fun.sboy.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.cj.protocol.Resultset;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JDBCUtil {

    //数据源
    public static DataSource dataSource = null;


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() {
        try {
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询sql返回一个对象
     *
     * @param sql
     * @param resultClass
     * @param parameters
     * @param <T>
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> T selectOne(String sql, Class<T> resultClass, String... parameters) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.length; i++) {
                preparedStatement.setString(i + 1, parameters[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Field[] fields = resultClass.getDeclaredFields();
                T type = resultClass.newInstance();
                //浅拷贝resultSet
                ResultSet finalResultSet = resultSet;
                Arrays.stream(fields).forEach(field -> {
                    field.setAccessible(true);
                    String name = field.getName();
                    name = fieldNameToSqlDataName(name);
                    try {
                        Object val = finalResultSet.getObject(name);
                        field.set(type, val);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
                return type;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            closeSource(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 查询并返回分页后的结果
     *
     * @param pageNum
     * @param tClass
     * @param <T>
     * @return
     */
    public static <T> List<T> selectListPage(String sql, Integer pageNum, Class<T> tClass) {
        ArrayList<T> list = new ArrayList<>();
        PreparedStatement pstt = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {
            connection = getConnection();
            pstt = connection.prepareStatement(sql);
            resultSet = pstt.executeQuery();
            Field[] fields = tClass.getDeclaredFields();
            //将查询出来的数据封装到对象中
            while (resultSet.next()) {
                T t = tClass.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String name = fieldNameToSqlDataName(field.getName());
                    Object val = resultSet.getObject(name);
                    field.set(t, val);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            closeSource(connection, pstt, resultSet);

        }


        return list;
    }


    /**
     * 添加记录
     *
     * @param sql
     * @param args
     * @return
     */
    public static Integer insert(String sql, Object... args) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement pstt = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pstt.setObject(i, args[i]);
            }
            int column = pstt.executeUpdate();
            connection.commit();
            return column;
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        }
        return 0;
    }

    /**
     * 变量名转换
     *
     * @param fieldName
     * @return
     */
    public static String fieldNameToSqlDataName(String fieldName) {

        Pattern compile = Pattern.compile("[A-Z]");
        Matcher matcher = compile.matcher(fieldName);
        while (matcher.find()) {
            String val = matcher.group();
            fieldName = fieldName.replace(val, "_" + val.toLowerCase(Locale.ROOT));
        }
        return fieldName;
    }

    /**
     * 关闭资源
     *
     * @param connection
     * @param preparedStatement
     * @param resultSet
     * @throws SQLException
     */
    public static void closeSource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
